﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;



using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.GCGBStapleDTSTableAdapters;


namespace BusinessLogicLayer
{
    public class GCGBStapleBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.GCGBStaple; } }

        private UserInformation userOrganization;

        private GCGBStapleMaster gcgbStapleMaster;

        private MyBindingList<GCGBStapleCollection> gcgbStapleCollectionList;

        private bool ReadonlyMode { get; set; }

        public GCGBStapleBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                gcgbStapleMaster = new GCGBStapleMaster();

                this.gcgbStapleCollectionList = new MyBindingList<GCGBStapleCollection>();


                GlobalDefaultValue.Apply(this);


                this.GCGBStapleMaster.PropertyChanged += new PropertyChangedEventHandler(GCGBStapleMaster_PropertyChanged);
                this.gcgbStapleCollectionList.ListChanged += new ListChangedEventHandler(GCGBStapleCollection_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void GCGBStapleMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (!this.ReadonlyMode && (e.PropertyName == "GCGBCommandID" || e.PropertyName == "IsDraft"))
            {
                this.StopTracking();
                this.GCGBStapleGetDetail();
                this.StartTracking();
            }

            this.SetDirty();
        }

        private void GCGBStapleCollection_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private GCGBStapleListingTableAdapter listingTableAdapter;
        protected GCGBStapleListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new GCGBStapleListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private GCGBStapleMasterTableAdapter masterTableAdapter;
        protected GCGBStapleMasterTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new GCGBStapleMasterTableAdapter();
                return masterTableAdapter;
            }
        }

        GCGBStapleCollectionTableAdapter collectionTableAdapter;
        protected GCGBStapleCollectionTableAdapter CollectionTableAdapter
        {
            get
            {
                if (collectionTableAdapter == null) collectionTableAdapter = new GCGBStapleCollectionTableAdapter();
                return collectionTableAdapter;
            }
        }


        GCGBStapleGetDetailTableAdapter getDetailTableAdapter;
        protected GCGBStapleGetDetailTableAdapter GetDetailTableAdapter
        {
            get
            {
                if (getDetailTableAdapter == null) getDetailTableAdapter = new GCGBStapleGetDetailTableAdapter();
                return getDetailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public GCGBStapleDTS.GCGBStapleListingDataTable GCGBStapleListing(DateTime lowerFillterDate, DateTime upperFillterDate)
        {
            return this.ListingTableAdapter.GetData(lowerFillterDate, upperFillterDate);
        }

        public GCGBStapleMaster GCGBStapleMaster
        {
            get { return this.gcgbStapleMaster; }
        }

        public MyBindingList<GCGBStapleCollection> GCGBStapleCollectionList
        {
            get { return this.gcgbStapleCollectionList; }
        }

        
        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int GCGBStapleID   //Primary Key
        {
            get { return this.GCGBStapleMaster.GCGBStapleID; }
            private set
            {
                if (this.GCGBStapleMaster.GCGBStapleID != value)
                {
                    this.StopTracking();

                    this.GCGBStapleMaster.GCGBStapleID = value;

                    this.GCGBStapleGetMaster();
                    this.GCGBStapleGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void GCGBStapleGetMaster()
        {
            if (this.GCGBStapleID > 0)
            {
                GCGBStapleDTS.GCGBStapleMasterDataTable masterDataTable = this.MasterTableAdapter.GetData(this.GCGBStapleID);

                if (masterDataTable.Count > 0)
                {
                    this.GCGBStapleMaster.StopTracking();

                    this.GCGBStapleMaster.GCGBCommandID = masterDataTable[0].GCGBCommandID;
                    this.GCGBStapleMaster.GCGBCommandReference = masterDataTable[0].GCGBCommandReference;
                    this.GCGBStapleMaster.GCGBCommandDate = masterDataTable[0].GCGBCommandDate;

                    this.GCGBStapleMaster.GCGBStapleDate = masterDataTable[0].GCGBStapleDate;
                    this.GCGBStapleMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.GCGBStapleMaster.GCGBStapleReference = masterDataTable[0].GCGBStapleReference;
                    this.GCGBStapleMaster.Description = masterDataTable[0].Description;

                    this.GCGBStapleMaster.PreparedEmployeeID = masterDataTable[0].PreparedEmployeeID;
                    this.GCGBStapleMaster.ApprovedEmployeeID = masterDataTable[0].ApprovedEmployeeID;

                    this.GCGBStapleMaster.Remarks = masterDataTable[0].Remarks;

                    this.GCGBStapleMaster.IsDraft = masterDataTable[0].IsDraft;

                    this.GCGBStapleMaster.StartTracking();

                    this.GCGBStapleMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.GCGBStapleMaster);
                this.GCGBStapleMaster.GCGBStapleDate = DateTime.Today;
                this.GCGBStapleMaster.EntryDate = DateTime.Today;
                this.GCGBStapleMaster.Reset();
            }
        }


        private void GCGBStapleGetDetail()
        {
            this.gcgbStapleCollectionList.RaiseListChangedEvents = false;
            this.gcgbStapleCollectionList.Clear();
            if (this.GCGBStapleID >= 0)
            {
                if (this.GCGBStapleMaster.GCGBCommandID > 0)
                {
                    GCGBStapleDTS.GCGBStapleGetDetailDataTable gcgbStapleGetDetailDataTable = this.GetDetailTableAdapter.GetData(this.GCGBStapleID, this.GCGBStapleMaster.GCGBCommandID, this.ReadonlyMode, this.GCGBStapleMaster.IsDraft);

                    if (gcgbStapleGetDetailDataTable.Rows.Count > 0)
                    {
                        int gcgbCommandID = 0; int gcgbCommandSerialID = 0;
                        foreach (GCGBStapleDTS.GCGBStapleGetDetailRow gcgbStapleGetDetailRow in gcgbStapleGetDetailDataTable.Rows)
                        {
                            if (gcgbStapleGetDetailRow.RSC == 0 || gcgbCommandID != gcgbStapleGetDetailRow.GCGBCommandID || gcgbCommandSerialID != gcgbStapleGetDetailRow.GCGBCommandSerialID)
                            {
                                gcgbCommandID = gcgbStapleGetDetailRow.GCGBCommandID; gcgbCommandSerialID = gcgbStapleGetDetailRow.GCGBCommandSerialID;
                                this.gcgbStapleCollectionList.Add(new GCGBStapleCollection(gcgbStapleGetDetailRow.GCGBCommandID, gcgbStapleGetDetailRow.GCGBCommandSerialID, gcgbStapleGetDetailRow.GCGBCommandDate, gcgbStapleGetDetailRow.WorkerNameID  , gcgbStapleGetDetailRow.MaterialSetID, gcgbStapleGetDetailRow.MaterialSemiID1, gcgbStapleGetDetailRow.QuantityPerSet1, gcgbStapleGetDetailRow.QuantitySemiBad1, gcgbStapleGetDetailRow.QuantitySemiLoss1, gcgbStapleGetDetailRow.QuantitySemiPreviousMinus1,
                                        gcgbStapleGetDetailRow.MaterialSemiID2, gcgbStapleGetDetailRow.QuantityPerSet2, gcgbStapleGetDetailRow.QuantitySemiBad2, gcgbStapleGetDetailRow.QuantitySemiLoss2, gcgbStapleGetDetailRow.QuantitySemiPreviousMinus2, gcgbStapleGetDetailRow.MaterialSemiID3, gcgbStapleGetDetailRow.QuantityPerSet3, gcgbStapleGetDetailRow.QuantitySemiBad3, gcgbStapleGetDetailRow.QuantitySemiLoss3, gcgbStapleGetDetailRow.QuantitySemiPreviousMinus3, gcgbStapleGetDetailRow.RemainsQuantity, gcgbStapleGetDetailRow.Quantity, gcgbStapleGetDetailRow.QuantityLoss, gcgbStapleGetDetailRow.Remarks));
                            }
                        }
                    }
                }
            }

            this.gcgbStapleCollectionList.RaiseListChangedEvents = true;
            this.gcgbStapleCollectionList.ResetBindings();
        }



        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<GCGBStapleCollection> inValidGCGBStapleCollection = this.GCGBStapleCollectionList.Where(gcgbStapleCollection => !gcgbStapleCollection.IsValid).ToList();
                return this.GCGBStapleMaster.IsValid && inValidGCGBStapleCollection.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (this.gcgbStapleMaster.GCGBStapleDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    if (!GlobalUserPermission.GetEditable("GCGBStapleApproved", this.GCGBStapleID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBStapleEditable", this.GCGBStapleID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (this.gcgbStapleMaster.GCGBStapleDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBStapleVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBStapleEditable", this.GCGBStapleID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (this.gcgbStapleMaster.GCGBStapleDate <= GlobalUserPermission.GlobalLockedDate()) return false;

                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.GCGBStapleUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("GCGBStapleEditable", this.GCGBStapleID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int gcgbStapleID)
        {
            this.ReadonlyMode = true;
            if (this.GCGBStapleID == gcgbStapleID) this.GCGBStapleID = -1; //Enforce to reload
            this.GCGBStapleID = gcgbStapleID;
        }

        public void New()
        {
            this.ReadonlyMode = false;
            if (this.GCGBStapleID == 0) this.GCGBStapleID = -1;
            this.GCGBStapleID = 0;
        }

        public void Edit()
        {
            this.ReadonlyMode = false;
            int gcgbStapleID = this.GCGBStapleID;

            this.GCGBStapleID = -1;
            this.GCGBStapleID = gcgbStapleID;
        }

        #region Save & Delete Method


        public bool Save()
        {
            int gcgbStapleID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref gcgbStapleID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(gcgbStapleID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient save", "Save conflict");

                    transactionScope.Complete();                    
                }

                this.Fill(gcgbStapleID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.gcgbStapleMaster.GCGBStapleID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.gcgbStapleMaster.GCGBStapleID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.gcgbStapleMaster.GCGBStapleID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    if (this.SaveConflict()) throw new System.ArgumentException("Insufficient delete", "Delete conflict");

                    transactionScope.Complete();                    
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.GCGBStapleMaster.PreparedEmployeeID, this.GCGBStapleMaster.GCGBStapleDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });


            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int gcgbStapleID)
        {
            GCGBStapleDTS.GCGBStapleMasterDataTable masterDataTable;
            GCGBStapleDTS.GCGBStapleMasterRow masterRow;

            if (this.gcgbStapleMaster.GCGBStapleID <= 0) //Add
            {
                masterDataTable = new GCGBStapleDTS.GCGBStapleMasterDataTable();
                masterRow = masterDataTable.NewGCGBStapleMasterRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(gcgbStapleMaster.GCGBStapleID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(gcgbStapleMaster.GCGBStapleID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.GCGBCommandID = this.gcgbStapleMaster.GCGBCommandID;

            masterRow.GCGBStapleDate = this.gcgbStapleMaster.GCGBStapleDate;
            masterRow.EntryDate = this.gcgbStapleMaster.EntryDate;

            masterRow.GCGBCommandReference = this.gcgbStapleMaster.GCGBCommandReference;
            masterRow.GCGBCommandDate = this.gcgbStapleMaster.GCGBCommandDate;

            masterRow.GCGBStapleReference = this.gcgbStapleMaster.GCGBStapleReference;
            masterRow.Description = this.gcgbStapleMaster.Description;

            masterRow.GCGBCommandTypeID = 1;

            masterRow.PreparedEmployeeID = this.gcgbStapleMaster.PreparedEmployeeID;
            masterRow.ApprovedEmployeeID = this.gcgbStapleMaster.ApprovedEmployeeID;

            masterRow.Remarks = this.gcgbStapleMaster.Remarks;

            masterRow.IsDraft = this.gcgbStapleMaster.IsDraft;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            masterRow.Approved = false;

            if (this.gcgbStapleMaster.GCGBStapleID <= 0) masterDataTable.AddGCGBStapleMasterRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            gcgbStapleID = masterRow.GCGBStapleID;

            return rowsAffected == 1;

        }

        private bool SaveDetail(int gcgbStapleID)
        {
            int serialID = 0; int rowsAffected = 0; int rowsPayment = 0;

            GCGBStapleDTS.GCGBStapleCollectionDataTable collectionDataTable = new GCGBStapleDTS.GCGBStapleCollectionDataTable();

            foreach (GCGBStapleCollection gcgbStapleCollection in this.gcgbStapleCollectionList)
            {
                if (gcgbStapleCollection.Quantity > 0)
                {
                    rowsPayment++;

                    GCGBStapleDTS.GCGBStapleCollectionRow collectionRow = collectionDataTable.NewGCGBStapleCollectionRow();

                    collectionRow.GCGBStapleID = gcgbStapleID;
                    collectionRow.SerialID = ++serialID;

                    collectionRow.GCGBStapleReference = this.gcgbStapleMaster.GCGBStapleReference;
                    collectionRow.GCGBStapleDate = this.gcgbStapleMaster.GCGBStapleDate;

                    collectionRow.GCGBCommandID = gcgbStapleCollection.GCGBCommandID;
                    collectionRow.GCGBCommandSerialID = gcgbStapleCollection.GCGBCommandSerialID;

                    collectionRow.GCGBCommandTypeID = 1;

                    collectionRow.WorkerNameID = gcgbStapleCollection.WorkerNameID;

                    collectionRow.MaterialSetID = gcgbStapleCollection.MaterialSetID;

                    collectionRow.Quantity = gcgbStapleCollection.Quantity;
                    collectionRow.QuantityLoss = gcgbStapleCollection.QuantityLoss;

                    collectionRow.MaterialSemiID1 = gcgbStapleCollection.MaterialSemiID1;
                    collectionRow.QuantityPerSet1 = gcgbStapleCollection.QuantityPerSet1;
                    collectionRow.QuantitySemi1 = 0;
                    collectionRow.QuantitySemiBad1 = gcgbStapleCollection.QuantitySemiBad1;
                    collectionRow.QuantitySemiLoss1 = gcgbStapleCollection.QuantitySemiLoss1;
                    collectionRow.QuantitySemiPreviousMinus1 = gcgbStapleCollection.QuantitySemiPreviousMinus1;

                    collectionRow.MaterialSemiID2 = gcgbStapleCollection.MaterialSemiID2;
                    collectionRow.QuantityPerSet2 = gcgbStapleCollection.QuantityPerSet2;
                    collectionRow.QuantitySemi2 = 0;
                    collectionRow.QuantitySemiBad2 = gcgbStapleCollection.QuantitySemiBad2;
                    collectionRow.QuantitySemiLoss2 = gcgbStapleCollection.QuantitySemiLoss2;
                    collectionRow.QuantitySemiPreviousMinus2 = gcgbStapleCollection.QuantitySemiPreviousMinus2;

                    collectionRow.MaterialSemiID3 = gcgbStapleCollection.MaterialSemiID3;
                    collectionRow.QuantityPerSet3 = gcgbStapleCollection.QuantityPerSet3;
                    collectionRow.QuantitySemi3 = 0;
                    collectionRow.QuantitySemiBad3 = gcgbStapleCollection.QuantitySemiBad3;
                    collectionRow.QuantitySemiLoss3 = gcgbStapleCollection.QuantitySemiLoss3;
                    collectionRow.QuantitySemiPreviousMinus3 = gcgbStapleCollection.QuantitySemiPreviousMinus3;


                    collectionRow.Remarks = gcgbStapleCollection.Remarks;

                    collectionRow.Approved = false;
                    collectionRow.InActive = false;

                    collectionDataTable.AddGCGBStapleCollectionRow(collectionRow);
                }
            }

            rowsAffected = this.CollectionTableAdapter.Update(collectionDataTable);
            if (rowsAffected != rowsPayment) throw new System.ArgumentException("Insufficient save", "Save detail");


            int rowsUpdated = this.CollectionTableAdapter.GCGBStapleSaveUpdate(gcgbStapleID, (int)GlobalMsADO.UpdateOption.SaveUpdate);
            if (rowsUpdated > rowsPayment) throw new System.ArgumentException("Insufficient save", "Save update");


        #endregion <Item Category>


            return true;
        }


        private bool SaveUndo(int gcgbStapleID)
        {
            int rowsUpdated = this.CollectionTableAdapter.GCGBStapleSaveUpdate(gcgbStapleID, (int)GlobalMsADO.UpdateOption.SaveUndo);
            int rowsDeleted = this.CollectionTableAdapter.Delete(gcgbStapleID);

            if (rowsUpdated > rowsDeleted) throw new System.ArgumentException("Insufficient save", "Save undo");

            return true;
        }


        private bool SaveConflict()
        {
            return GlobalDataObjectAction.SaveConflict("GCGBStapleSaveConflict", this.GCGBStapleID);
        }



        #region Import Excel

        public bool ImportGCGBStaple(string fileName)
        {
            try
            {
                if (this.ReadOnly) throw new System.ArgumentException("Import", "Permission conflict"); // || !this.IsDirty

                double paymentAmount = 0;
                ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

                DataTable excelDataTable = OleDbDatabase.OpenExcelSheet(OleDbDatabase.MappingTaskID.GCGBStaple, fileName);
                if (excelDataTable != null && excelDataTable.Rows.Count > 0)
                {
                    //Step 1: Check all rows to validate imported data
                    foreach (DataRow excelDataRow in excelDataTable.Rows)
                    {
                        if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                        {
                            if (paymentAmount > 0)
                            {
                                GCGBStapleCollection gcgbStapleCollection = this.GCGBStapleCollectionList.FindItem(TypeDescriptor.CreateProperty(typeof(GCGBStapleCollection), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                if (gcgbStapleCollection == null || gcgbStapleCollection.RemainsQuantity < Math.Round(gcgbStapleCollection.Quantity + paymentAmount, GlobalVariables.Round0Quantity))
                                    exceptionTable.AddException(new string[] { gcgbStapleCollection == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                            }
                        }
                        else
                            exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                    }

                    //Step 2: Start import
                    if (exceptionTable.Table.Rows.Count <= 0)
                    {
                        this.GCGBStapleCollectionList.RaiseListChangedEvents = false;

                        foreach (DataRow excelDataRow in excelDataTable.Rows)
                        {
                            if (excelDataRow["CustomerID"].ToString() != "" && double.TryParse(excelDataRow["PaymentAmount"].ToString(), out paymentAmount))
                            {
                                if (paymentAmount > 0)
                                {
                                    GCGBStapleCollection gcgbStapleCollection = this.GCGBStapleCollectionList.FindItem(TypeDescriptor.CreateProperty(typeof(GCGBStapleCollection), "CustomerCode", typeof(string)), excelDataRow["CustomerID"].ToString().Trim());
                                    if (gcgbStapleCollection == null || gcgbStapleCollection.RemainsQuantity < Math.Round(gcgbStapleCollection.Quantity + paymentAmount, GlobalVariables.Round0Quantity))
                                        exceptionTable.AddException(new string[] { gcgbStapleCollection == null ? "Customer not found" : "Out of balance", excelDataRow["CustomerID"].ToString() + " [" + paymentAmount.ToString("N0") + "]" });
                                    else
                                    {
                                        gcgbStapleCollection.Quantity = gcgbStapleCollection.Quantity + paymentAmount;
                                        gcgbStapleCollection.Remarks = excelDataRow["Remarks"].ToString();
                                    }
                                }
                            }
                            else
                                exceptionTable.AddException(new string[] { "Invalid customer code or payment amount value", excelDataRow["CustomerID"].ToString() + " [" + excelDataRow["PaymentAmount"].ToString() + "]" });
                        }

                        this.GCGBStapleCollectionList.RaiseListChangedEvents = true;
                        this.GCGBStapleCollectionList.ResetBindings();
                    }

                    if (exceptionTable.Table.Rows.Count > 0)
                        throw new CustomException("Invalid data value or data entry not found", exceptionTable.Table);
                }
                return true;
            }
            catch (System.Exception exception)
            {
                this.GCGBStapleCollectionList.RaiseListChangedEvents = true;
                throw exception;
            }
        }

        #endregion Import Excel



        private void RestoreProcedure()
        {

            string queryString;
            string[] queryArray;


            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          LEFT(DATENAME(Month, GCGBStapleMaster.GCGBStapleDate), 3) + ', ' + DATENAME(Year, GCGBStapleMaster.GCGBStapleDate) AS Month, * " + "\r\n";
            queryString = queryString + "       FROM            GCGBStapleMaster " + "\r\n";
            queryString = queryString + "       WHERE           GCGBStapleMaster.GCGBStapleDate >= @LowerFillterDate AND GCGBStapleMaster.GCGBStapleDate <= @UpperFillterDate " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleListing", queryString);



            queryString = " @GCGBStapleID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          GCGBStapleMaster.GCGBStapleID, GCGBStapleMaster.GCGBStapleDate, GCGBStapleMaster.GCGBStapleReference, GCGBStapleMaster.GCGBCommandID, GCGBCommandMaster.GCGBCommandReference, GCGBCommandMaster.GCGBCommandDate, GCGBStapleMaster.GCGBCommandTypeID, GCGBStapleMaster.EntryDate, GCGBStapleMaster.PreparedEmployeeID, GCGBStapleMaster.ApprovedEmployeeID, GCGBStapleMaster.Description, GCGBStapleMaster.Remarks, GCGBStapleMaster.UserID, GCGBStapleMaster.UserOrganizationID, GCGBStapleMaster.Approved, GCGBStapleMaster.IsDraft " + "\r\n";
            queryString = queryString + "       FROM            GCGBStapleMaster INNER JOIN GCGBCommandMaster ON GCGBStapleMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID " + "\r\n";
            queryString = queryString + "       WHERE           GCGBStapleID = @GCGBStapleID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleMasterSelect", queryString);


            queryString = " @GCGBStapleDate datetime, @GCGBStapleReference nvarchar(50), @GCGBCommandID int, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Description nvarchar(150), @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBStapleMaster (GCGBStapleDate, GCGBStapleReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved, IsDraft) VALUES (@GCGBStapleDate, @GCGBStapleReference, @GCGBCommandID, @GCGBCommandTypeID, @EntryDate, @PreparedEmployeeID, @ApprovedEmployeeID, @Description, @Remarks, @UserID, @UserOrganizationID, @Approved, @IsDraft) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBStapleID, GCGBStapleDate, GCGBStapleReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved FROM GCGBStapleMaster WHERE (GCGBStapleID = SCOPE_IDENTITY()) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleMasterInsert", queryString);


            queryString = " @GCGBStapleID int, @GCGBStapleDate datetime, @GCGBStapleReference nvarchar(50), @GCGBCommandID int, @GCGBCommandTypeID int, @EntryDate datetime, @PreparedEmployeeID int, @ApprovedEmployeeID int, @Description nvarchar(150), @Remarks nvarchar(100), @UserID int, @UserOrganizationID int, @Approved bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBStapleMaster SET GCGBStapleDate = @GCGBStapleDate, GCGBStapleReference = @GCGBStapleReference, GCGBCommandID = @GCGBCommandID, GCGBCommandTypeID = @GCGBCommandTypeID, EntryDate = @EntryDate, PreparedEmployeeID = @PreparedEmployeeID, ApprovedEmployeeID = @ApprovedEmployeeID, Description = @Description, Remarks = @Remarks, UserID = @UserID, UserOrganizationID = @UserOrganizationID, Approved = @Approved, IsDraft = @IsDraft WHERE GCGBStapleID = @GCGBStapleID " + "\r\n";
            queryString = queryString + "       SELECT          GCGBStapleID, GCGBStapleDate, GCGBStapleReference, GCGBCommandID, GCGBCommandTypeID, EntryDate, PreparedEmployeeID, ApprovedEmployeeID, Description, Remarks, UserID, UserOrganizationID, Approved FROM GCGBStapleMaster WHERE (GCGBStapleID = @GCGBStapleID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleMasterUpdate", queryString);


            queryString = " @GCGBStapleID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     [GCGBStapleMaster] WHERE GCGBStapleID = @GCGBStapleID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleMasterDelete", queryString);




            queryString = " @GCGBStapleID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT          GCGBStapleID, SerialID, GCGBStapleReference, GCGBStapleDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID , MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive " + "\r\n";
            queryString = queryString + "       FROM            GCGBStapleCollection " + "\r\n";
            queryString = queryString + "       WHERE           GCGBStapleID = @GCGBStapleID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleCollectionSelect", queryString);


            queryString = " @GCGBStapleID int, @SerialID int, @GCGBStapleReference nvarchar(50), @GCGBStapleDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @GCGBCommandTypeID int, @WorkerNameID  int, @MaterialSetID int, @Quantity float, @QuantityLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiBad1 float, @QuantitySemiLoss1 float, @QuantitySemiPreviousMinus1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiBad2 float, @QuantitySemiLoss2 float, @QuantitySemiPreviousMinus2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiBad3 float, @QuantitySemiLoss3 float, @QuantitySemiPreviousMinus3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO     GCGBStapleCollection (GCGBStapleID, SerialID, GCGBStapleReference, GCGBStapleDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID , MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1,  QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2,  QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3,  QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive) " + "\r\n";
            queryString = queryString + "                   VALUES (@GCGBStapleID, @SerialID, @GCGBStapleReference, @GCGBStapleDate, @GCGBCommandID, @GCGBCommandSerialID, @GCGBCommandTypeID, @WorkerNameID , @MaterialSetID, @Quantity, @QuantityLoss, @MaterialSemiID1, @QuantityPerSet1, @QuantitySemi1, @QuantitySemiBad1, @QuantitySemiLoss1, @QuantitySemiPreviousMinus1, @MaterialSemiID2, @QuantityPerSet2, @QuantitySemi2, @QuantitySemiBad2, @QuantitySemiLoss2, @QuantitySemiPreviousMinus2, @MaterialSemiID3, @QuantityPerSet3, @QuantitySemi3, @QuantitySemiBad3, @QuantitySemiLoss3, @QuantitySemiPreviousMinus3, @Remarks, @Approved, @InActive) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBStapleID, SerialID, GCGBStapleReference, GCGBStapleDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID , MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive FROM GCGBStapleCollection WHERE (GCGBStapleID = @GCGBStapleID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleCollectionInsert", queryString);


            queryString = " @GCGBStapleID int, @SerialID int, @GCGBStapleReference nvarchar(50), @GCGBStapleDate datetime, @GCGBCommandID int, @GCGBCommandSerialID int, @GCGBCommandTypeID int, @WorkerNameID  int, @MaterialSetID int, @Quantity float, @QuantityLoss float, @MaterialSemiID1 int, @QuantityPerSet1 float, @QuantitySemi1 float, @QuantitySemiBad1 float, @QuantitySemiLoss1 float, @QuantitySemiPreviousMinus1 float, @MaterialSemiID2 int, @QuantityPerSet2 float, @QuantitySemi2 float, @QuantitySemiBad2 float, @QuantitySemiLoss2 float, @QuantitySemiPreviousMinus2 float, @MaterialSemiID3 int, @QuantityPerSet3 float, @QuantitySemi3 float, @QuantitySemiBad3 float, @QuantitySemiLoss3 float, @QuantitySemiPreviousMinus3 float, @Remarks nvarchar(150), @Approved bit, @InActive bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBStapleCollection SET GCGBStapleReference = @GCGBStapleReference, GCGBStapleDate = @GCGBStapleDate, GCGBCommandID = @GCGBCommandID, GCGBCommandSerialID = @GCGBCommandSerialID, GCGBCommandTypeID = @GCGBCommandTypeID, WorkerNameID  = @WorkerNameID , MaterialSetID = @MaterialSetID, Quantity = @Quantity, QuantityLoss = @QuantityLoss, MaterialSemiID1 = @MaterialSemiID1, QuantityPerSet1 = @QuantityPerSet1, QuantitySemi1 = @QuantitySemi1, QuantitySemiBad1 = @QuantitySemiBad1,  QuantitySemiLoss1 = @QuantitySemiLoss1, QuantitySemiPreviousMinus1 = @QuantitySemiPreviousMinus1, " + "\r\n";
            queryString = queryString + "                 MaterialSemiID2 = @MaterialSemiID2, QuantityPerSet2 = @QuantityPerSet2, QuantitySemi2 = @QuantitySemi2, QuantitySemiBad2 = @QuantitySemiBad2, QuantitySemiLoss2 = @QuantitySemiLoss2, QuantitySemiPreviousMinus2 = @QuantitySemiPreviousMinus2, MaterialSemiID3 = @MaterialSemiID3, QuantityPerSet3 = @QuantityPerSet3, QuantitySemi3 = @QuantitySemi3, QuantitySemiBad3 = @QuantitySemiBad3, QuantitySemiLoss3 = @QuantitySemiLoss3, QuantitySemiPreviousMinus3 = @QuantitySemiPreviousMinus3, Remarks = @Remarks, Approved = @Approved, InActive = @InActive WHERE (GCGBStapleID = @GCGBStapleID) AND (SerialID = @SerialID) " + "\r\n";
            queryString = queryString + "       SELECT          GCGBStapleID, SerialID, GCGBStapleReference, GCGBStapleDate, GCGBCommandID, GCGBCommandSerialID, GCGBCommandTypeID, WorkerNameID , MaterialSetID, Quantity, QuantityLoss, MaterialSemiID1, QuantityPerSet1, QuantitySemi1, QuantitySemiBad1, QuantitySemiLoss1, QuantitySemiPreviousMinus1, MaterialSemiID2, QuantityPerSet2, QuantitySemi2, QuantitySemiBad2, QuantitySemiLoss2, QuantitySemiPreviousMinus2, MaterialSemiID3, QuantityPerSet3, QuantitySemi3, QuantitySemiBad3, QuantitySemiLoss3, QuantitySemiPreviousMinus3, Remarks, Approved, InActive FROM GCGBStapleCollection WHERE (GCGBStapleID = @GCGBStapleID) AND (SerialID = @SerialID) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleCollectionUpdate", queryString);

            queryString = " @GCGBStapleID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM     GCGBStapleCollection WHERE GCGBStapleID = @GCGBStapleID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleCollectionDelete", queryString);


            string queryEdit; string queryNew;

            queryEdit = "               SELECT          GCGBCommandCollection.GCGBCommandID, GCGBCommandCollection.SerialID AS GCGBCommandSerialID, GCGBCommandCollection.GCGBCommandReference, GCGBCommandCollection.GCGBCommandDate, GCGBStapleCollection.WorkerNameID , GCGBCommandCollection.MaterialSetID, ROUND(GCGBCommandCollection.Quantity *  @IsDraft + GCGBCommandCollection.QuantityPlaster * (1 - @IsDraft) - GCGBCommandCollection.QuantityStaple - GCGBCommandCollection.QuantityStapleLoss + GCGBStapleCollection.Quantity + GCGBStapleCollection.QuantityLoss, " + GlobalVariables.Round0Amount.ToString() + ") AS RemainsQuantity, GCGBStapleCollection.Quantity, GCGBStapleCollection.QuantityLoss, " + "\r\n";
            queryEdit = queryEdit + "                   GCGBCommandCollection.MaterialSemiID1, GCGBCommandCollection.QuantityPerSet1, GCGBStapleCollection.QuantitySemiBad1, GCGBStapleCollection.QuantitySemiLoss1, GCGBStapleCollection.QuantitySemiPreviousMinus1, GCGBCommandCollection.MaterialSemiID2, GCGBCommandCollection.QuantityPerSet2, GCGBStapleCollection.QuantitySemiBad2, GCGBStapleCollection.QuantitySemiLoss2, GCGBStapleCollection.QuantitySemiPreviousMinus2, GCGBCommandCollection.MaterialSemiID3, GCGBCommandCollection.QuantityPerSet3, GCGBStapleCollection.QuantitySemiBad3, GCGBStapleCollection.QuantitySemiLoss3, GCGBStapleCollection.QuantitySemiPreviousMinus3, GCGBStapleCollection.Remarks, 0 AS RSC " + "\r\n";
            queryEdit = queryEdit + "   FROM            GCGBCommandCollection INNER JOIN " + "\r\n";
            queryEdit = queryEdit + "                   GCGBStapleCollection ON GCGBCommandCollection.GCGBCommandID = GCGBStapleCollection.GCGBCommandID AND GCGBCommandCollection.SerialID = GCGBStapleCollection.GCGBCommandSerialID " + "\r\n";
            queryEdit = queryEdit + "   WHERE           (@GCGBCommandID <= 0 OR @GCGBCommandID = GCGBCommandCollection.GCGBCommandID) AND GCGBStapleCollection.GCGBStapleID = @GCGBStapleID " + "\r\n";

            queryNew = "                SELECT          GCGBCommandCollection.GCGBCommandID, GCGBCommandCollection.SerialID AS GCGBCommandSerialID, GCGBCommandCollection.GCGBCommandReference, GCGBCommandCollection.GCGBCommandDate, 0 AS WorkerNameID , GCGBCommandCollection.MaterialSetID, ROUND(GCGBCommandCollection.Quantity * @IsDraft + GCGBCommandCollection.QuantityPlaster * (1 - @IsDraft) - GCGBCommandCollection.QuantityStaple - GCGBCommandCollection.QuantityStapleLoss, " + GlobalVariables.Round0Amount.ToString() + ") AS RemainsQuantity, 0 AS Quantity, 0 AS QuantityLoss, " + "\r\n";
            queryNew = queryNew + "                     GCGBCommandCollection.MaterialSemiID1, GCGBCommandCollection.QuantityPerSet1, 0 AS QuantitySemiBad1, 0 AS QuantitySemiLoss1, 0 AS QuantitySemiPreviousMinus1, GCGBCommandCollection.MaterialSemiID2, GCGBCommandCollection.QuantityPerSet2, 0 AS QuantitySemiBad2, 0 AS QuantitySemiLoss2, 0 AS QuantitySemiPreviousMinus2, GCGBCommandCollection.MaterialSemiID3, GCGBCommandCollection.QuantityPerSet3, 0 AS QuantitySemiBad3, 0 AS QuantitySemiLoss3, 0 AS QuantitySemiPreviousMinus3, '' AS Remarks, 1 AS RSC " + "\r\n";
            queryNew = queryNew + "     FROM            GCGBCommandCollection " + "\r\n";
            queryNew = queryNew + "     WHERE           (@GCGBCommandID <= 0 OR @GCGBCommandID = GCGBCommandCollection.GCGBCommandID) AND GCGBCommandCollection.Approved = 1 AND ROUND(GCGBCommandCollection.Quantity * @IsDraft + GCGBCommandCollection.QuantityPlaster * (1 - @IsDraft) - GCGBCommandCollection.QuantityStaple - GCGBCommandCollection.QuantityStapleLoss, " + GlobalVariables.Round0Amount.ToString() + ") > 0 " + "\r\n";

            queryString = " @GCGBStapleID Int, @GCGBCommandID Int, @ReadonlyMode bit, @IsDraft bit " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + " IF (@GCGBStapleID <= 0) " + "\r\n";
            queryString = queryString + "    BEGIN " + "\r\n";
            queryString = queryString + "       " + queryNew + "\r\n";
            queryString = queryString + "        ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "    END " + "\r\n";
            queryString = queryString + " ELSE " + "\r\n";

            queryString = queryString + "   IF (@ReadonlyMode = 1) " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";
            queryString = queryString + "   ELSE " + "\r\n"; //FULL SELECT FOR EDIT MODE

            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           " + queryEdit + "\r\n";
            queryString = queryString + "           UNION ALL " + "\r\n";
            queryString = queryString + "           " + queryNew + "\r\n";
            queryString = queryString + "           ORDER BY GCGBCommandCollection.GCGBCommandDate, GCGBCommandCollection.GCGBCommandID, GCGBCommandSerialID, RSC " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("GCGBStapleGetDetail", queryString);



            queryString = " @GCGBStapleID int, @UpdateOption int " + "\r\n"; //UpdateOption: 1 Savedetail, -1:Saveundo
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE          GCGBCommandCollection " + "\r\n";
            queryString = queryString + "       SET             GCGBCommandCollection.QuantityStaple = ROUND(GCGBCommandCollection.QuantityStaple + GCGBStapleCollection.Quantity * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantityStapleLoss = ROUND(GCGBCommandCollection.QuantityStapleLoss + GCGBStapleCollection.QuantityLoss * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), " + "\r\n";
            queryString = queryString + "                  GCGBCommandCollection.QuantitySemiStapleBad1 = ROUND(GCGBCommandCollection.QuantitySemiStapleBad1 + GCGBStapleCollection.QuantitySemiBad1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiStapleLoss1 = ROUND(GCGBCommandCollection.QuantitySemiStapleLoss1 + GCGBStapleCollection.QuantitySemiLoss1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiStaplePreviousMinus1 = ROUND(GCGBCommandCollection.QuantitySemiStaplePreviousMinus1 + GCGBStapleCollection.QuantitySemiPreviousMinus1 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), " + "\r\n";
            queryString = queryString + "                  GCGBCommandCollection.QuantitySemiStapleBad2 = ROUND(GCGBCommandCollection.QuantitySemiStapleBad2 + GCGBStapleCollection.QuantitySemiBad2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiStapleLoss2 = ROUND(GCGBCommandCollection.QuantitySemiStapleLoss2 + GCGBStapleCollection.QuantitySemiLoss2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiStaplePreviousMinus2 = ROUND(GCGBCommandCollection.QuantitySemiStaplePreviousMinus2 + GCGBStapleCollection.QuantitySemiPreviousMinus2 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + ")," + "\r\n";
            queryString = queryString + "                  GCGBCommandCollection.QuantitySemiStapleBad3 = ROUND(GCGBCommandCollection.QuantitySemiStapleBad3 + GCGBStapleCollection.QuantitySemiBad3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiStapleLoss3 = ROUND(GCGBCommandCollection.QuantitySemiStapleLoss3 + GCGBStapleCollection.QuantitySemiLoss3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + "), GCGBCommandCollection.QuantitySemiStaplePreviousMinus3 = ROUND(GCGBCommandCollection.QuantitySemiStaplePreviousMinus3 + GCGBStapleCollection.QuantitySemiPreviousMinus3 * @UpdateOption, " + GlobalVariables.Round0Amount.ToString() + ")" + "\r\n";
            queryString = queryString + "       FROM           (SELECT GCGBCommandID, GCGBCommandSerialID, SUM(Quantity) AS Quantity, SUM(QuantityLoss) As QuantityLoss, SUM(QuantitySemiBad1) AS QuantitySemiBad1, SUM(QuantitySemiLoss1) AS QuantitySemiLoss1, SUM(QuantitySemiPreviousMinus1) AS QuantitySemiPreviousMinus1, SUM(QuantitySemiBad2) AS QuantitySemiBad2, SUM(QuantitySemiLoss2) AS QuantitySemiLoss2, SUM(QuantitySemiPreviousMinus2) AS QuantitySemiPreviousMinus2, SUM(QuantitySemiBad3) AS QuantitySemiBad3, SUM(QuantitySemiLoss3) AS QuantitySemiLoss3, SUM(QuantitySemiPreviousMinus3) AS QuantitySemiPreviousMinus3  FROM GCGBStapleCollection WHERE GCGBStapleID = @GCGBStapleID GROUP BY GCGBCommandID, GCGBCommandSerialID) GCGBStapleCollection INNER JOIN " + "\r\n";
            queryString = queryString + "                       GCGBCommandCollection ON GCGBStapleCollection.GCGBCommandID = GCGBCommandCollection.GCGBCommandID AND GCGBStapleCollection.GCGBCommandSerialID = GCGBCommandCollection.SerialID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("GCGBStapleSaveUpdate", queryString);




            /// <summary>
            /// Check for save conflict
            /// </summary>
            queryArray = new string[1];                            
            queryArray[0] = " SELECT TOP 1  GCGBStapleMaster.GCGBStapleID FROM GCGBStapleMaster INNER JOIN GCGBCommandMaster ON GCGBStapleMaster.GCGBCommandID = GCGBCommandMaster.GCGBCommandID AND GCGBStapleMaster.GCGBStapleDate < GCGBCommandMaster.GCGBCommandDate ";
            //queryArray[1] = " SELECT TOP 1  GCGBCommandID FROM GCGBCommandCollection WHERE (ROUND(Quantity * " + this.GCGBStapleMaster.IsDraft.CompareTo(true).ToString() + " + QuantityPlaster * (1 - " + this.GCGBStapleMaster.IsDraft.CompareTo(true).ToString() + ") - QuantityStaple - QuantityStapleLoss, " + GlobalVariables.Round0Amount.ToString() + ") < 0) ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBStapleSaveConflict", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = " SELECT TOP 1 GCGBStapleID FROM GCGBStapleMaster WHERE GCGBStapleID = @FindIdentityID AND Approved = 1 ";

            SQLDatabase.CreateProcedureToCheckExisting("GCGBStapleApproved", queryArray);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[0];

            SQLDatabase.CreateProcedureToCheckExisting("GCGBStapleEditable");// queryArray);


        }

    }

}

